package jdbc;

import java.sql.*;

public class JdbcDemo {

    public static void main(String[] args) throws Exception {
        print("JDBC example started.");

        // 创建jdbc连接
        try (Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/")) {
            print("Connected to server.");

            // 创建数据库对象
            try (Statement stmt = conn.createStatement()) {
                // 创建基于复制模式的CIty表
                stmt.executeUpdate("CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) " +
                        "WITH \"template=replicated\"");

                // 创建带有一个备份基于分片模式的person表
                stmt.executeUpdate("CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, " +
                        "PRIMARY KEY (id, city_id)) WITH \"backups=1, affinity_key=city_id\"");

                // 创建索引
                stmt.executeUpdate("CREATE INDEX on Person (city_id)");
            }

            print("Created database objects.");

            // 往city表中插入数据
            try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO city (id, name) VALUES (?, ?)")) {
                stmt.setLong(1, 1L);
                stmt.setString(2, "Forest Hill");
                stmt.executeUpdate();

                stmt.setLong(1, 2L);
                stmt.setString(2, "Denver");
                stmt.executeUpdate();

                stmt.setLong(1, 3L);
                stmt.setString(2, "St. Petersburg");
                stmt.executeUpdate();
            }

            // 往person表中插入数据
            try (PreparedStatement stmt =
                         conn.prepareStatement("INSERT INTO person (id, name, city_id) values (?, ?, ?)")) {
                stmt.setLong(1, 1L);
                stmt.setString(2, "John Doe");
                stmt.setLong(3, 3L);
                stmt.executeUpdate();

                stmt.setLong(1, 2L);
                stmt.setString(2, "Jane Roe");
                stmt.setLong(3, 2L);
                stmt.executeUpdate();

                stmt.setLong(1, 3L);
                stmt.setString(2, "Mary Major");
                stmt.setLong(3, 1L);
                stmt.executeUpdate();

                stmt.setLong(1, 4L);
                stmt.setString(2, "Richard Miles");
                stmt.setLong(3, 2L);
                stmt.executeUpdate();
            }

            print("Populated data.");

            // 查询数据
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs =
                             stmt.executeQuery("SELECT p.name, c.name FROM Person p INNER JOIN City c on c.id = p.city_id")) {
                    print("Query results:");

                    while (rs.next()) {
                        System.out.println(">>>    " + rs.getString(1) + ", " + rs.getString(2));
                    }
                }
            }

            // 清楚表数据
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("DROP TABLE Person");
                stmt.executeUpdate("DROP TABLE City");
            }

            print("Dropped database objects.");
        }

        print("JDBC example finished.");
    }

    /**
     * Prints message.
     *
     * @param msg Message to print before all objects are printed.
     */
    private static void print(String msg) {
        System.out.println();
        System.out.println(">>> " + msg);
    }

}
